var express = require('express')
var router = express.Router()

// 导入db，操作数据库
const { getAll, getById, exec } = require('../db')

/**
 * 获取所有分类
 * GET /category
 */

router.get('/', async (req, res) => {
  // console.log(req);
  console.log(req.query)
  // 解析请求参数
  // 解构query对象,提取page,size
  // 如果没有值,就给默认值1,2
  const { page = 1, size = 3 } = req.query
  // 获取总数
  let sql = `select count(*) as total  from bk_category`
  const obj = await getById(sql) // { total: 10 }
  console.log(obj)

  // 根据分页公式, 计算offset偏移量
  let offset = (page - 1) * size
  // 操作数据库
  sql = `select * from bk_category order by category_id limit ${offset}, ${size}`
  const data = await getAll(sql)

  res.send({
    code: 0,
    message: '获取分类成功',
    result: {
      total: obj.total,
      data: data,
    },
  })
})

/**
 * 根据分类id获取该分类下的文章
 * GET /category/:id
 */
router.get('/:id', async (req, res) => {
  const { id } = req.params
  let sql = `SELECT * FROM bk_category WHERE category_id=${id}`
  const category = await getById(sql)
  sql = `SELECT * FROM bk_articles WHERE category_id=${id}`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取单个成功',
    result: {
      category,
      data,
    },
  })
})
/**
 * 根据分类id获取该分类的分类描述
 * GET /category/only/:id
 */
router.get('/only/:id', async (req, res) => {
  const { id } = req.params
  let sql = `select category_id,category_name,category_description,category_pic from bk_category where category_id = ${id}`
  const data = await getById(sql)
  res.send({
    code: 0,
    message: '获取分类成功',
    result: {
      data,
    },
  })
})

/**
 * 添加分类
 * POST /category
 */
router.post('/', async (req, res) => {
  const { category_name, category_description, category_pic } = req.body
  let sql = `INSERT INTO bk_category (category_name, category_description,category_pic) VALUES ('${category_name}','${category_description}','${category_pic}')`
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '添加分类成功',
    result: {
      category_id: data.insertId,
      category_name,
      category_description,
      category_pic,
    },
  })
})

/**
 * 修改分类
 * PUT /category/:id
 */
router.put('/:id', async (req, res) => {
  const { id } = req.params
  const { category_name, category_description, category_pic } = req.body
  let sql = `UPDATE bk_category SET category_name='${category_name}',category_description='${category_description}', category_pic='${category_pic}' WHERE category_id=${id}`
  await exec(sql)
  res.send({
    code: 0,
    message: '修改分类成功',
    result: {
      id,
      category_name,
      category_description,
      category_pic,
    },
  })
})

/**
 * 删除分类
 * DELETE /category/:id
 */
router.delete('/:id', async (req, res) => {
  const { id } = req.params
  let sql = `DELETE FROM bk_category WHERE category_id=${id}`
  await exec(sql)
  res.send({
    code: 0,
    message: '删除分类成功',
    result: '',
  })
})

module.exports = router
